<?php

namespace App\Controller\Admin;

use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class BigdataExcelController extends BaseController
{
    /**
     * 处理大数据数据，添加年月并处理成sql
     * @return \Psr\Http\Message\ResponseInterface
     */
    public function addYearMonthAndToSql(): \Psr\Http\Message\ResponseInterface
    {
        $filename = $this->request->input('file_path');
        //添加年月
        $this->bigdataAddTime($filename);
        //转成sql
        $sql = $this->bigdataToSql($filename);
        return $this->success(['sql' => $sql]);
    }

    //添加年月
    public function bigdataAddTime($filename)
    {
        $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
        $reader->setReadDataOnly(true);
        $spreadsheet = $reader->load($filename);
        //设置月和年
        $year = date('Y');
        $month = date('m');
        //表名和对应的月年列
        $sheetName = ['常驻人口结构' => ['G','H'], '常驻人口画像' => ['H','I'], '客流指数' => ['J','K'], '客流画像'=>['H','I'], '业态分布' => ['E','F'], '垂类业态分布' => ['E','F'], '交通设施' => ['E','F'], '消费指数' =>['F','G'], '热力图' => ['E','F'], '竞品分析' => ['F','G']];
        foreach ($sheetName as $key => $val) {
            $sheet = $spreadsheet->getSheetByName($key);
            $rows = $sheet->getHighestRow();
            $sheet->getCell($val[0] . 1)->setValue('月');
            $sheet->getCell($val[1] . 1)->setValue('年');
            for ($i = 2; $i <= $rows; $i++) {
                //如果首行为空那么就肯定结束了
                if (empty($sheet->getCell('A' . $i)->getValue())) {
                    continue;
                } else {
                    //设置数据
                    $sheet->getCell($val[0] . $i)->setValue($month);
                    $sheet->getCell($val[1] . $i)->setValue($year);
                }
            }
        }
        $writer = new Xlsx($spreadsheet);
        $writer->save($filename);
        return $filename;
    }

    /**
     *
     */
    public function bigdataToSql($filename)
    {
        $sql = '';
        //处理文件（当前只处理前6个表）
        $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
        $reader->setReadDataOnly(true);
        $spreadsheet = $reader->load($filename);
        //循环读取所有的sheet
        //常驻人口结构
        $resident_population =  ['town','personnel_type','month_value','last_three_month_value','last_six_month_value','last_twelve_month_value','month','year'];
        //常驻人口画像
        $population_portrait =  ['town','personnel_type','tag_category','tag_name','attr','value','tgi','month','year'];
        //客流指数
        $flow_index =  ['town','type','time_type','result_granularity','time_attr','month_value','last_three_month_value','last_six_month_value','last_twelve_month_value','month','year'];
        //客流画像
        $passenger_flow_portrait =  ['town','personnel_type','tag_category','tag_name','attr','value','tgi','month','year'];
        //业态分布
        $business_distribution =  ['town','profession','business_name','value','month','year'];
        //垂直业态分布
        $vertical_business_distribution =  ['town','poi_category','level','value','month','year'];
        //交通设施
        $traffic_facilities = ['town','type','data_item','value','month','year'];
        //消费指数
        $consumption_index = ['town','fist_class','second_class','capita_price','tgi','month','year'];
        //热力图
        $hotmap = ['town','type','coordinate','weight','month','year'];
        //竞品分析
        $competitive_product_analysis = ['town','brand','store','address','popularity','month','year'];
        //常驻人口结构
        $sheet = $spreadsheet->getSheetByName('常驻人口结构');
        $rows  = $sheet->getHighestRow();
        $totalValues = [];
        for ($i = 2; $i <= $rows; $i++) {
            //如果首行为空那么就肯定结束了
            if (empty($sheet->getCell('A' . $i)->getValue())) {
                continue;
            }
            $values[] = $sheet->getCell('A' . $i)->getValue();
            $values[] = $sheet->getCell('B' . $i)->getValue();
            $values[] = $sheet->getCell('C' . $i)->getValue();
            $values[] = $sheet->getCell('D' . $i)->getValue();
            $values[] = $sheet->getCell('E' . $i)->getValue();
            $values[] = $sheet->getCell('F' . $i)->getValue();
            $values[] = $sheet->getCell('G' . $i)->getValue();
            $values[] = $sheet->getCell('H' . $i)->getValue();
            foreach ($values as &$item) {
                if ($item !== '' && !is_null($item)) {
                    $item = "'{$item}'";
                }else {
                    $item = 'null';
                }
            }
            $res = implode(',',$values);
            $totalValues[] = '(' . $res . ')';
            $values = [];
        }
        if (!empty($totalValues)) {
            $totalValues = implode(',',$totalValues);
            $column = implode(',',$resident_population);
            $sql .= "INSERT INTO tencent_lbs_resident_population ($column) VALUES {$totalValues};";
        }
        //常驻人口画像
        $sheet = $spreadsheet->getSheetByName('常驻人口画像');
        $rows  = $sheet->getHighestRow();
        $totalValues = [];
        for ($i = 2; $i <= $rows; $i++) {
            //如果首行为空那么就肯定结束了
            if (empty($sheet->getCell('A' . $i)->getValue())) {
                continue;
            }
            $values[] = $sheet->getCell('A' . $i)->getValue();
            $values[] = $sheet->getCell('B' . $i)->getValue();
            $values[] = $sheet->getCell('C' . $i)->getValue();
            $values[] = $sheet->getCell('D' . $i)->getValue();
            $values[] = $sheet->getCell('E' . $i)->getValue();
            $values[] = $sheet->getCell('F' . $i)->getValue();
            $values[] = $sheet->getCell('G' . $i)->getValue();
            $values[] = $sheet->getCell('H' . $i)->getValue();
            $values[] = $sheet->getCell('I' . $i)->getValue();
            foreach ($values as &$item) {
                if ($item !== '' && !is_null($item)) {
                    $item = "'{$item}'";
                }else {
                    $item = 'null';
                }
            }
            $res = implode(',',$values);
            $totalValues[] = '(' . $res . ')';
            $values = [];
        }
        if (!empty($totalValues)) {
            $totalValues = implode(',',$totalValues);
            $column = implode(',',$population_portrait);
            $sql .= "INSERT INTO tencent_lbs_population_portrait ($column) VALUES {$totalValues};";
        }
        //客流指数
        $sheet = $spreadsheet->getSheetByName('客流指数');
        $rows  = $sheet->getHighestRow();
        $totalValues = [];
        for ($i = 2; $i <= $rows; $i++) {
            //如果首行为空那么就肯定结束了
            if (empty($sheet->getCell('A' . $i)->getValue())) {
                continue;
            }
            $values[] = $sheet->getCell('A' . $i)->getValue();
            $values[] = $sheet->getCell('B' . $i)->getValue();
            $values[] = $sheet->getCell('C' . $i)->getValue();
            $values[] = $sheet->getCell('D' . $i)->getValue();
            $values[] = $sheet->getCell('E' . $i)->getValue();
            $values[] = $sheet->getCell('F' . $i)->getValue();
            $values[] = $sheet->getCell('G' . $i)->getValue();
            $values[] = $sheet->getCell('H' . $i)->getValue();
            $values[] = $sheet->getCell('I' . $i)->getValue();
            $values[] = $sheet->getCell('J' . $i)->getValue();
            $values[] = $sheet->getCell('K' . $i)->getValue();
            foreach ($values as &$item) {
                if ($item !== '' && !is_null($item)) {
                    $item = "'{$item}'";
                }else {
                    $item = 'null';
                }
            }
            $res = implode(',',$values);
            $totalValues[] = '(' . $res . ')';
            $values = [];
        }
        if (!empty($totalValues)) {
            $totalValues = implode(',',$totalValues);
            $column = implode(',',$flow_index);
            $sql .= "INSERT INTO tencent_lbs_passenger_flow_index ($column) VALUES {$totalValues};";
        }
        //客流画像
        $sheet = $spreadsheet->getSheetByName('客流画像');
        $rows  = $sheet->getHighestRow();
        $totalValues = [];
        for ($i = 2; $i <= $rows; $i++) {
            //如果首行为空那么就肯定结束了
            if (empty($sheet->getCell('A' . $i)->getValue())) {
                continue;
            }
            $values[] = $sheet->getCell('A' . $i)->getValue();
            $values[] = $sheet->getCell('B' . $i)->getValue();
            $values[] = $sheet->getCell('C' . $i)->getValue();
            $values[] = $sheet->getCell('D' . $i)->getValue();
            $values[] = $sheet->getCell('E' . $i)->getValue();
            $values[] = $sheet->getCell('F' . $i)->getValue();
            $values[] = $sheet->getCell('G' . $i)->getValue();
            $values[] = $sheet->getCell('H' . $i)->getValue();
            $values[] = $sheet->getCell('I' . $i)->getValue();
            foreach ($values as &$item) {
                if ($item !== '' && !is_null($item)) {
                    $item = "'{$item}'";
                }else {
                    $item = "''";
                }
            }
            $res = implode(',',$values);
            $totalValues[] = '(' . $res . ')';
            $values = [];
        }
        if (!empty($totalValues)) {
            $totalValues = implode(',',$totalValues);
            $column = implode(',',$passenger_flow_portrait);
            $sql .= "INSERT INTO tencent_lbs_passenger_flow_portrait ($column) VALUES {$totalValues};";
        }
        //业态分布
        $sheet = $spreadsheet->getSheetByName('业态分布');
        $rows  = $sheet->getHighestRow();
        $totalValues = [];
        for ($i = 2; $i <= $rows; $i++) {
            //如果首行为空那么就肯定结束了
            if (empty($sheet->getCell('A' . $i)->getValue())) {
                continue;
            }
            $values[] = $sheet->getCell('A' . $i)->getValue();
            $values[] = $sheet->getCell('B' . $i)->getValue();
            $values[] = $sheet->getCell('C' . $i)->getValue();
            $values[] = $sheet->getCell('D' . $i)->getValue();
            $values[] = $sheet->getCell('E' . $i)->getValue();
            $values[] = $sheet->getCell('F' . $i)->getValue();
            foreach ($values as &$item) {
                if ($item !== '' && !is_null($item)) {
                    $item = "'{$item}'";
                }else {
                    $item = 'null';
                }
            }
            $res = implode(',',$values);
            $totalValues[] = '(' . $res . ')';
            $values = [];
        }
        if (!empty($totalValues)) {
            $totalValues = implode(',',$totalValues);
            $column = implode(',',$business_distribution);
            $sql .= "INSERT INTO tencent_lbs_business_distribution ($column) VALUES {$totalValues};";
        }
        //垂直业态分布
        $sheet = $spreadsheet->getSheetByName('垂类业态分布');
        $rows  = $sheet->getHighestRow();
        $totalValues = [];
        for ($i = 2; $i <= $rows; $i++) {
            //如果首行为空那么就肯定结束了
            if (empty($sheet->getCell('A' . $i)->getValue())) {
                continue;
            }
            $values[] = $sheet->getCell('A' . $i)->getValue();
            $values[] = $sheet->getCell('B' . $i)->getValue();
            $values[] = $sheet->getCell('C' . $i)->getValue();
            $values[] = $sheet->getCell('D' . $i)->getValue();
            $values[] = $sheet->getCell('E' . $i)->getValue();
            $values[] = $sheet->getCell('F' . $i)->getValue();
            foreach ($values as &$item) {
                if ($item !== '' && !is_null($item)) {
                    $item = "'{$item}'";
                }else {
                    $item = 'null';
                }
            }
            $res = implode(',',$values);
            $totalValues[] = '(' . $res . ')';
            $values = [];
        }
        if (!empty($totalValues)) {
            $totalValues = implode(',',$totalValues);
            $column = implode(',',$vertical_business_distribution);
            $sql .= "INSERT INTO tencent_lbs_vertical_business_distribution ($column) VALUES {$totalValues};";
        }
        //交通设施
        $sheet = $spreadsheet->getSheetByName('交通设施');
        $rows  = $sheet->getHighestRow();
        $totalValues = [];
        for ($i = 2; $i <= $rows; $i++) {
            //如果首行为空那么就肯定结束了
            if (empty($sheet->getCell('A' . $i)->getValue())) {
                continue;
            }
            $values[] = $sheet->getCell('A' . $i)->getValue();
            $values[] = $sheet->getCell('B' . $i)->getValue();
            $values[] = $sheet->getCell('C' . $i)->getValue();
            $values[] = $sheet->getCell('D' . $i)->getValue();
            $values[] = $sheet->getCell('E' . $i)->getValue();
            $values[] = $sheet->getCell('F' . $i)->getValue();
            foreach ($values as &$item) {
                if ($item !== '' && !is_null($item)) {
                    $item = "'{$item}'";
                }else {
                    $item = 'null';
                }
            }
            $res = implode(',',$values);
            $totalValues[] = '(' . $res . ')';
            $values = [];
        }
        if (!empty($totalValues)) {
            $totalValues = implode(',',$totalValues);
            $column = implode(',',$traffic_facilities);
            $sql .= "INSERT INTO tencent_lbs_traffic_facilities ($column) VALUES {$totalValues};";
        }
        //消费指数
        $sheet = $spreadsheet->getSheetByName('消费指数');
        $rows  = $sheet->getHighestRow();
        $totalValues = [];
        for ($i = 2; $i <= $rows; $i++) {
            //如果首行为空那么就肯定结束了
            if (empty($sheet->getCell('A' . $i)->getValue())) {
                continue;
            }
            $values[] = $sheet->getCell('A' . $i)->getValue();
            $values[] = $sheet->getCell('B' . $i)->getValue();
            $values[] = $sheet->getCell('C' . $i)->getValue();
            $values[] = $sheet->getCell('D' . $i)->getValue();
            $values[] = $sheet->getCell('E' . $i)->getValue();
            $values[] = $sheet->getCell('F' . $i)->getValue();
            $values[] = $sheet->getCell('G' . $i)->getValue();
            foreach ($values as &$item) {
                if ($item !== '' && !is_null($item)) {
                    $item = "'{$item}'";
                }else {
                    $item = 'null';
                }
            }
            $res = implode(',',$values);
            $totalValues[] = '(' . $res . ')';
            $values = [];
        }
        if (!empty($totalValues)) {
            $totalValues = implode(',',$totalValues);
            $column = implode(',',$consumption_index);
            $sql .= "INSERT INTO tencent_lbs_consumption_index ($column) VALUES {$totalValues};";
        }
        //热力图
        $sheet = $spreadsheet->getSheetByName('热力图');
        $rows  = $sheet->getHighestRow();
        $totalValues = [];
        for ($i = 2; $i <= $rows; $i++) {
            //如果首行为空那么就肯定结束了
            if (empty($sheet->getCell('A' . $i)->getValue())) {
                continue;
            }
            $values[] = $sheet->getCell('A' . $i)->getValue();
            $values[] = $sheet->getCell('B' . $i)->getValue();
            $values[] = $sheet->getCell('C' . $i)->getValue();
            $values[] = $sheet->getCell('D' . $i)->getValue();
            $values[] = $sheet->getCell('E' . $i)->getValue();
            $values[] = $sheet->getCell('F' . $i)->getValue();
            foreach ($values as &$item) {
                if ($item !== '' && !is_null($item)) {
                    $item = "'{$item}'";
                }else {
                    $item = 'null';
                }
            }
            $res = implode(',',$values);
            $totalValues[] = '(' . $res . ')';
            $values = [];
        }
        if (!empty($totalValues)) {
            $totalValues = implode(',',$totalValues);
            $column = implode(',',$hotmap);
            $sql .= "INSERT INTO tencent_lbs_hotmap ($column) VALUES {$totalValues};";
        }
        //竞品分析
        $sheet = $spreadsheet->getSheetByName('竞品分析');
        $rows  = $sheet->getHighestRow();
        $totalValues = [];
        for ($i = 2; $i <= $rows; $i++) {
            //如果首行为空那么就肯定结束了
            if (empty($sheet->getCell('A' . $i)->getValue())) {
                continue;
            }
            $values[] = $sheet->getCell('A' . $i)->getValue();
            $values[] = $sheet->getCell('B' . $i)->getValue();
            $values[] = $sheet->getCell('C' . $i)->getValue();
            $values[] = $sheet->getCell('D' . $i)->getValue();
            $values[] = $sheet->getCell('E' . $i)->getValue();
            $values[] = $sheet->getCell('F' . $i)->getValue();
            $values[] = $sheet->getCell('G' . $i)->getValue();
            foreach ($values as &$item) {
                if ($item !== '' && !is_null($item)) {
                    $item = "'{$item}'";
                }else {
                    $item = 'null';
                }
            }
            $res = implode(',',$values);
            $totalValues[] = '(' . $res . ')';
            $values = [];
        }
        if (!empty($totalValues)) {
            $totalValues = implode(',',$totalValues);
            $column = implode(',',$competitive_product_analysis);
            $sql .= "INSERT INTO tencent_lbs_competitive_product_analysis ($column) VALUES {$totalValues};";
        }
        unset($spreadsheet);
        return $sql;
    }

    /**
     * 读取文件
     * @return \Psr\Http\Message\ResponseInterface
     */
    public function readExcel()
    {
        $filePath = $this->request->input('path');
        try {
            // 加载文件
            $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
            $reader->setReadDataOnly(true);
            $spreadsheet = $reader->load($filePath);
            $data = [];
            // 遍历所有 sheet
            foreach ($spreadsheet->getAllSheets() as $sheet) {
                $sheetData = [];
                $highestRow = $sheet->getHighestRow();
                $highestColumn = $sheet->getHighestColumn();
                for ($row = 1; $row <= $highestRow; $row++) {
                    $rowData = [];
                    for ($col = 'A'; $col <= $highestColumn; $col++) {
                        $cell = $sheet->getCell($col . $row);
                        $rowData[] = $cell->getValue();
                    }
                    $sheetData[] = $rowData;
                }
                $data[] = $sheetData;
            }
            return $this->success($data);
        } catch (\Exception $e) {
            return $this->error($e->getMessage());
        }
    }
}